﻿package yii.db.schema{

	import yii.db.*;
	import phpas3.*;
	import yii.db.schema.*;
	import yii.db.schema.sqlite.*;

	public class CDbCommandBuilder{

		static const PARAM_PREFIX:String = ':yp';
		private var _schema:CDbSchema;
		private var _connection:CDbConnection;

		public function CDbCommandBuilder( schema:CDbSchema ){
			this._schema = schema;
			this._connection = schema.getDbConnection();
		}




	/**
	 * Creates a COUNT(*) command for a single table+
	 * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string)+
	 * @param CDbCriteria $criteria the query criteria
	 * @param string $alias the alias name of the primary table+ Defaults to 't'+
	 * @return CDbCommand query command+
	 */
	public function createCountCommand($table,$criteria,$alias='t'):CDbCommand{
		this.ensureTable($table);
		if($criteria.alias != ''){
			$alias = $criteria.alias;
		}
		$alias = this._schema.quoteTableName( $alias );

		var $sql:String = "";
		if(!empty($criteria.group) || !empty($criteria.having)){
			var $select = is_array($criteria.select) ? implode(', ',$criteria.select) : $criteria.select;
			if($criteria.alias!=''){
				$alias=$criteria.alias;
			}
			$sql = ($criteria.distinct ? 'SELECT DISTINCT':'SELECT') +" "+ $select +" FROM "+ $table.rawName +" "+ $alias;
			$sql=this.applyJoin($sql,$criteria.join);
			$sql=this.applyCondition($sql,$criteria.condition);
			$sql=this.applyGroup($sql,$criteria.group);
			$sql=this.applyHaving($sql,$criteria.having);
			$sql = "SELECT COUNT(*) FROM ("+ $sql +") sq";
		} else {
			if(is_string($criteria.select) && stripos($criteria.select,'count')===0){
				$sql = "SELECT "+$criteria.select;
			} else if($criteria.distinct){
				if(is_array($table.primaryKey)){
					var $pk_ary:Array = new Array();
					for( var index in $table.primaryKey ){
						var $key = $table.primaryKey[index];
						$pk_ary.push( $alias +'.'+ $key );
					}
					var $pk:String = implode( ', ', $pk_ary );
				} else {
					var $pk:String = $alias +'.'+ $table.primaryKey;
				}
				$sql = "SELECT COUNT(DISTINCT "+ $pk +")";
			} else {
				$sql="SELECT COUNT(*)";
			}
			$sql+=" FROM "+ $table.rawName +" "+ $alias;
			$sql=this.applyJoin($sql,$criteria.join);
			$sql=this.applyCondition($sql,$criteria.condition);
		}

		var $command:CDbCommand = this._connection.createCommand($sql);
		this.bindValues( $command,$criteria.params );
		return $command;
	}






	/**
	 * Creates an UPDATE command+
	 * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string)+
	 * @param array $data list of columns to be updated (name=>value)
	 * @param CDbCriteria $criteria the query criteria
	 * @return CDbCommand update command+
	 */
	public function createUpdateCommand( $table:CDbTableSchema, $data:Object, $criteria:CDbCriteria ):CDbCommand{
		this.ensureTable( $table );
		var $fields:Array = new Array();
		var $values:Array = new Array();
		var $bindByPosition = isset($criteria.params[0]);
		var $i:int = 0;
		for( var $name in $data ){
			var $value = $data[$name];
			var $column:CDbColumnSchema = $table.getColumn($name);
			if( $column !== null ){
				if( $value is CDbExpression ){
					$fields.push( $column.rawName +'='+ $value.expression );
					for( var $n in $value.params ){
						var $v = $value.params[$n];
						$values[$n] = $v;
					}
				} else if( $bindByPosition ){
					$fields.push( $column.rawName +'=?' );
					$values.push( $column.typecast($value) );
				} else {
					$fields.push( $column.rawName +'='+ CDbCommandBuilder.PARAM_PREFIX+$i );
					$values[CDbCommandBuilder.PARAM_PREFIX+$i] = $column.typecast($value);
					$i++;
				}
			}
		}
		if( $fields.length==0 ){
			throw new Error( "No columns are being updated for table for table「"+ $table.name +"」" );
		}
		var $sql:String = "UPDATE "+ $table.rawName +" SET "+ implode(', ',$fields);
		$sql = this.applyJoin( $sql, $criteria.join );
		$sql = this.applyCondition( $sql, $criteria.condition );
		$sql = this.applyOrder( $sql, $criteria.order );
		$sql = this.applyLimit( $sql, $criteria.limit, $criteria.offset );

		var $command:CDbCommand = this._connection.createCommand( $sql );
		//trace( "createUpdateCommand2", $values, $criteria.params );
		this.bindValues( $command, array_merge( $values, $criteria.params ) );

		return $command;
	}








	/**
	 * Returns the last insertion ID for the specified table+
	 * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string)+
	 * @return mixed last insertion id+ Null is returned if no sequence name+
	 */
	public function getLastInsertID($table:CDbTableSchema):*{
		this.ensureTable($table);
		if($table.sequenceName!==null)
			return this._connection.getLastInsertID($table.sequenceName);
		else
			return null;
	}



	/**
	 * Creates an INSERT command+
	 * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string)+
	 * @param array $data data to be inserted (column name=>column value)+ If a key is not a valid column name, the corresponding value will be ignored+
	 * @return CDbCommand insert command
	 */
	public function createInsertCommand( $table:CDbTableSchema, $data:Object ):CDbCommand{
		this.ensureTable($table);
		var $fields:Array = new Array();
		var $values:Array = new Array();
		var $placeholders:Array = new Array();
		var $i:int = 0;
		for( var $name in $data ){
			var $value = $data[$name];
			var $column:CDbColumnSchema = $table.getColumn( $name );
			if( $column!==null && ($value!==null || $column.allowNull) ){
				$fields.push( $column.rawName );
				if( $value is CDbExpression ){
					$placeholders.push( $value.expression );
					for( var $n in $value.params ){
						var $v = $value.params[$n];
						$values[$n]=$v;
					}
				} else {
					$placeholders.push( CDbCommandBuilder.PARAM_PREFIX+$i );
					$values[CDbCommandBuilder.PARAM_PREFIX+$i] = $column.typecast($value);
					$i++;
				}
			}
		}
		if( $fields.length==0 ){
			var $pks:Array = is_array($table.primaryKey) ? $table.primaryKey : [ $table.primaryKey ];
			for( var index in $pks ){
				var $pk = $pks[index];
				$fields.push( $table.getColumn($pk).rawName );
				$placeholders.push( 'NULL' );
			}
		}
		var $sql:String = "INSERT INTO "+ $table.rawName +" ("+ implode(', ',$fields) +") VALUES ("+ implode(', ',$placeholders) +')';
		var $command:CDbCommand = this._connection.createCommand( $sql );

		for( var $name in $values ){
			var $value = $values[$name];
			$command.bindValue($name,$value);
		}

		return $command;
	}









	/**
	 * Generates the expression for selecting rows with specified composite key values.
	 * @param CDbTableSchema $table the table schema
	 * @param array $values list of primary key values to be selected within
	 * @param string $prefix column prefix (ended with dot)
	 * @return string the expression for selection
	 * @since 1.0.4
	 */
	protected function createCompositeInCondition( $table:CDbTableSchema, $values:Array, $prefix:String ):String{
		var $keyNames:Array = new Array();
		//foreach(array_keys($values[0]) as $name){
		for( var index in array_keys($values[0]) ){
			var $name = array_keys($values[0])[index];
			$keyNames.push( $prefix+$table.columns[$name].rawName );
		}
		var $vs = new Array();
		//foreach($values as $value)
		for( var index in $values ){
			var $value = $values[index];
			$vs.push( '('+implode(', ',$value)+')' );
		}
		return '('+implode(', ',$keyNames)+') IN ('+implode(', ',$vs)+')';
	}





	/**
	 * Generates the expression for selecting rows of specified primary key values.
	 * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
	 * @param mixed $columnName the column name(s). It can be either a string indicating a single column
	 * or an array of column names. If the latter, it stands for a composite key.
	 * @param array $values list of key values to be selected within
	 * @param string $prefix column prefix (ended with dot). If null, it will be the table name
	 * @return string the expression for selection
	 * @since 1.0.4
	 */
	public function createInCondition( $table:*, $columnName:*, $values:Array, $prefix=null):String{
		var $n:int = count( $values );
		if( $n < 1 ){
			return '0=1';
		}
		this.ensureTable( $table );

		if( $prefix===null ){
			$prefix = $table.rawName +'.';
		}

		var $db:CDbConnection = this._connection;

		if( is_array($columnName) && count($columnName)===1 ){
			var $columnName:* = reset( $columnName );
		}
		if( is_string($columnName) ){ // simple key
			if( !isset($table.columns[$columnName]) ){
				throw new Error( 'Table 「'+ $table.name +'」 does not have a column named 「'+ $columnName +'」.' );
			}
			var $column:CSqliteColumnSchema = $table.columns[$columnName];
			for( var index in $values ){
				$values[index] = $column.typecast( $values[index] );
				if( is_string($values[index]) ){
					$values[index] = $db.quoteValue( $values[index] );
				}
			}
			if( $n===1 ){
				return $prefix + $column.rawName + ($values[0]===null?' IS NULL':'='+ $values[0]);
			} else {
				return $prefix + $column.rawName +' IN ('+ implode(', ',$values) +')';
			}
		} else if( is_array($columnName) ){
			for( var index in $columnName ){
				var $name = $columnName[index];
				if(!isset($table.columns[$name])){
					throw new Error( 'Table 「'+ $table.name +'」 does not have a column named 「'+ $name +'」.' );
				}

				for( var $i:uint=0; $i<$n; ++$i ){
					if(isset($values[$i][$name])){
						$value = $table.columns[$name].typecast($values[$i][$name]);
						if(is_string($value))
							$values[$i][$name]=$db.quoteValue($value);
						else
							$values[$i][$name]=$value;
					} else {
						throw new Error( 'The value for the column 「'+ $name +'」 is not supplied when querying the table 「'+ $table.name +'」.' );
					}
				}
			}
			if(count($values)===1){
				var $entries = new Array();
				for( var $name in $values[0] ){
					var $value = $values[0][$name];
					$entries.push( $prefix + $table.columns[$name].rawName + ($value===null?' IS NULL':'='+ $value) );
				}
				return implode(' AND ',$entries);
			}
			return this.createCompositeInCondition( $table, $values, $prefix );
		} else {
			throw new Error( "Column name must be either a string or an array." );
		}
		return "";
	}








	/**
	 * Alters the SQL to apply ORDER BY.
	 * @param string $sql SQL statement without ORDER BY.
	 * @param string $orderBy column ordering
	 * @return string modified SQL applied with ORDER BY.
	 */
	public function applyOrder( $sql:String, $orderBy:String ):String{
		if($orderBy!='')
			return $sql +' ORDER BY '+ $orderBy;
		else
			return $sql;
	}




	/**
	 * Creates a SELECT command for a single table.
	 * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
	 * @param CDbCriteria $criteria the query criteria
	 * @param string $alias the alias name of the primary table. Defaults to 't'.
	 * @return CDbCommand query command.
	 */
	public function createFindCommand( $table:*, $criteria:CDbCriteria, $alias:String='t' ):CDbCommand{
		this.ensureTable($table);
		var $select=is_array($criteria.select) ? implode(', ',$criteria.select) : $criteria.select;
		if($criteria.alias!='')
			$alias=$criteria.alias;
		$alias=this._schema.quoteTableName($alias);

		// issue 1432: need to expand * when SQL has JOIN
		if($select==='*' && !empty($criteria.join)){
			var $prefix=$alias +'.';
			$select = new Array();
			//foreach($table.getColumnNames() as $name)
			for( var index in $table.getColumnNames ){
				var $name = $table.getColumnNames[index];
				$select.push( $prefix + this._schema.quoteColumnName($name) );
			}
			$select=implode(', ',$select);
		}

		var $sql:String =($criteria.distinct ? 'SELECT DISTINCT':'SELECT') +" "+ $select +" FROM "+ $table.rawName +" "+ $alias;
		$sql=this.applyJoin($sql,$criteria.join);
		$sql=this.applyCondition($sql,$criteria.condition);
		$sql=this.applyGroup($sql,$criteria.group);
		$sql=this.applyHaving($sql,$criteria.having);
		$sql=this.applyOrder($sql,$criteria.order);
		$sql=this.applyLimit($sql,$criteria.limit,$criteria.offset);
		var $command=this._connection.createCommand($sql);
		this.bindValues($command,$criteria.params);
		return $command;
	}




	/**
	 * Creates a query criteria with the specified primary key.
	 * @param mixed $table the table schema ({@link CDbTableSchema}) or the table name (string).
	 * @param mixed $pk primary key value(s). Use array for multiple primary keys. For composite key, each key value must be an array (column name=>column value).
	 * @param mixed $condition query condition or criteria.
	 * If a string, it is treated as query condition;
	 * If an array, it is treated as the initial values for constructing a {@link CDbCriteria};
	 * Otherwise, it should be an instance of {@link CDbCriteria}.
	 * @param array $params parameters to be bound to an SQL statement.
	 * This is only used when the second parameter is a string (query condition).
	 * In other cases, please use {@link CDbCriteria::params} to set parameters.
	 * @param string $prefix column prefix (ended with dot). If null, it will be the table name
	 * @return CDbCriteria the created query criteria
	 */
	public function createPkCriteria( $table:*, $pk:*, $condition:*='', $params:Array=null, $prefix:String=null ):CDbCriteria{
		this.ensureTable( $table );
		var $criteria = this.createCriteria( $condition, $params );
		if( $criteria.alias != '' ){
			$prefix = this._schema.quoteTableName( $criteria.alias ) +'.';
		}
		if( !is_array($pk) ){ // single key
			$pk = [ $pk ];
		}
		if( is_array($table.primaryKey) && !isset($pk[0]) && count($pk)==0 ){ // single composite key
			$pk = [ $pk ];
		}
		$condition = this.createInCondition( $table, $table.primaryKey, $pk, $prefix );
		if( $criteria.condition != '' ){
			$criteria.condition = $condition +' AND ('+ $criteria.condition +')';
		} else {
			$criteria.condition = $condition;
		}
		return $criteria;
	}

















        public function applyLimit( $sql:String, $limit, $offset ):String{
                if($limit>=0)
                        $sql +=' LIMIT '+ int($limit);
                if($offset>0)
                        $sql +=' OFFSET '+ int($offset);
                return $sql;
        }


		public function bindValues( command:CDbCommand, values:Array ):void{
			var n:uint = count(values);
			if( n===0 ){
				return;
			}
			if( values[0] ){
				for( var i:uint=0; i<n; ++i ){
					command.bindValue( i+1, values[i] );
				}
			} else {
				//foreach( values as name=>value ){
				for( var name in values ){
					if( name.charAt(0) !== ':' ){
						name = ':'+ name;
					}
					command.bindValue( name, values[name] );
				}
			}
		}




		public function applyJoin( sql:String, join:String ):String{
			if( join != '' ){
				return sql +' '+ join;
			} else {
				return sql;
			}
		}
		public function applyCondition( sql:String, condition:String ):String{
			if( condition != ''){
				return sql +' WHERE '+ condition;
			} else {
				return sql;
			}
		}
		public function applyGroup( sql:String, group:String ):String{
			if(group!=''){
				return sql +' GROUP BY '+ group;
			} else {
				return sql;
			}
		}
		public function applyHaving( sql:String, having:String ):String{
			if(having!=''){
				return sql +' HAVING '+ having;
			} else {
				return sql;
			}
		}

		public function createCriteria( condition='', params=null){
			if( params === null ){
				params = new Array();
			}
			var criteria:CDbCriteria;
			if( typeof(condition)=="array" ){
				criteria = new CDbCriteria( condition );
			} else if( condition is CDbCriteria ){
				criteria = condition;
			} else {
				criteria = new CDbCriteria;
				criteria.condition = condition;
				criteria.params = params;
			}
			return criteria;
		}


/*
		public function createCountCommand( table, criteria, alias:String='t' ){
			this.ensureTable( table );
			if( criteria.alias != '' ){
				alias = criteria.alias;
			}
			alias = this._schema.quoteTableName( alias );

			var sql:String = "";

			if( criteria.group || criteria.having ){
				var select:String = criteria.select is Array ? criteria.select.join(", ") : criteria.select;
				if( criteria.alias != '' ){
					alias = criteria.alias;
				}
				sql = (criteria.distinct ? "SELECT DISTINCT":"SELECT") +" "+ select +" FROM "+ table.rawName +" $alias";
				sql = this.applyJoin( sql, criteria.join );
				sql = this.applyCondition( sql, criteria.condition );
				sql = this.applyGroup( sql, criteria.group );
				sql = this.applyHaving( sql, criteria.having );
				sql = "SELECT COUNT(*) FROM (sql) sq";
			} else {
				if( criteria.select is String && criteria.select.search(/count/i)===0 ){
					sql = "SELECT "+ criteria.select;
				} else if( criteria.distinct ){
					if( table.primaryKey is Array ){
						var pk_array:Array = new Array();
						for( var key in table.primaryKey ){
							pk_array.push( alias +'.'+ key );
						}
						var pk:String = pk_array.join( ', ' );
					} else{
						var pk:String = alias +'.'+ table.primaryKey;
					}
					sql = "SELECT COUNT(DISTINCT "+ pk +")";
				} else {
					sql = "SELECT COUNT(*)";
				}
				sql += " FROM "+ table.rawName +" "+ alias;
				sql = this.applyJoin( sql,criteria.join );
				sql = this.applyCondition( sql, criteria.condition );
			}

			var command:CDbCommand = this._connection.createCommand( sql );
			this.bindValues( command, criteria.params );
			return command;
		}
*/



		protected function ensureTable( table ){
//			if(is_string($table) && ($table=this._schema.getTable($tableName=$table))===null){
//				//throw new CDbException(Yii::t('yii','Table "{table}" does not exist.',array('{table}'=>$tableName)));
//			}
		}



	}
}